﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Web;
using System.Configuration;

namespace Hutech.Authentication.Library.Data
{
    internal static class DBUserRoles
    {
        /// <summary>
        /// Gets the connection string for read.
        /// </summary>
        /// <returns></returns>
        private static string GetReadConnectionString()
        {
            return ConfigurationManager.AppSettings["MSSQLConnectionString_CustommerService27032013"];
        }

        /// <summary>
        /// Gets the connection string for write.
        /// </summary>
        /// <returns></returns>
        private static string GetWriteConnectionString()
        {
            if (ConfigurationManager.AppSettings["MSSQLWriteConnectionString_CustommerService27032013"] != null)
            {
                return ConfigurationManager.AppSettings["MSSQLWriteConnectionString_CustommerService27032013"];
            }

            return ConfigurationManager.AppSettings["MSSQLConnectionString_CustommerService27032013"];
        }


        /// <summary>
        /// Inserts a row in the root_UserRoles table. Returns rows affected count.
        /// </summary>
        /// <param name="id"> id </param>
        /// <param name="userId"> userId </param>
        /// <param name="roleId"> roleId </param>
        /// <returns>int</returns>
        public static int Create(
        Guid id,
        Guid userId,
        Guid roleId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetWriteConnectionString(), "root_UserRoles_Insert", 3);
            sph.DefineSqlParameter("@ID", SqlDbType.UniqueIdentifier, ParameterDirection.Input, id);
            sph.DefineSqlParameter("@UserId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userId);
            sph.DefineSqlParameter("@RoleId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, roleId);
            int rowsAffected = sph.ExecuteNonQuery();
            return rowsAffected;
        }


        /// <summary>
        /// Updates a row in the root_UserRoles table. Returns true if row updated.
        /// </summary>
        /// <param name="id"> id </param>
        /// <param name="userId"> userId </param>
        /// <param name="roleId"> roleId </param>
        /// <returns>bool</returns>
        public static bool Update(Guid userId, Guid roleId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetWriteConnectionString(), "root_UserRoles_Update", 2);
            sph.DefineSqlParameter("@UserId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userId);
            sph.DefineSqlParameter("@RoleId", SqlDbType.UniqueIdentifier, ParameterDirection.Input, roleId);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > 0);
        }

        /// <summary>
        /// Deletes a row from the root_UserRoles table. Returns true if row deleted.
        /// </summary>
        /// <param name="id"> id </param>
        /// <returns>bool</returns>
        public static bool Delete(Guid id)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetWriteConnectionString(), "root_UserRoles_Delete", 1);
            sph.DefineSqlParameter("@ID", SqlDbType.UniqueIdentifier, ParameterDirection.Input, id);
            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > 0);
        }

        /// <summary>
        /// Gets an IDataReader with one row from the root_UserRoles table.
        /// </summary>
        /// <param name="id"> id </param>
        public static IDataReader GetOne(Guid id)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "root_UserRoles_SelectOne", 1);
            sph.DefineSqlParameter("@ID", SqlDbType.UniqueIdentifier, ParameterDirection.Input, id);
            return sph.ExecuteReader();
        }

        /// <summary>
        /// Gets a count of rows in the root_UserRoles table.
        /// </summary>
        public static int GetCount()
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar(
            GetReadConnectionString(),
            CommandType.StoredProcedure,
            "root_UserRoles_GetCount",
            null));
        }

        /// <summary>
        /// Gets an IDataReader with all rows in the root_UserRoles table.
        /// </summary>
        public static IDataReader GetAll()
        {
            return SqlHelper.ExecuteReader(
            GetReadConnectionString(),
            CommandType.StoredProcedure,
            "root_UserRoles_SelectAll",
            null);
        }

        /// <summary>
        /// Gets a page of data from the root_UserRoles table.
        /// </summary>
        /// <param name="pageNumber">The page number.</param>
        /// <param name="pageSize">Size of the page.</param>
        /// <param name="totalPages">total pages</param>
        public static IDataReader GetPage(
        int pageNumber,
        int pageSize,
        out int totalPages)
        {
            totalPages = 1;
            int totalRows
            = GetCount();

            if (pageSize > 0)
                totalPages = totalRows / pageSize;

            if (totalRows <= pageSize)
            {
                totalPages = 1;
            }
            else
            {
                int remainder;
                Math.DivRem(totalRows, pageSize, out remainder);
                if (remainder > 0)
                {
                    totalPages += 1;
                }
            }
            SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "root_UserRoles_SelectPage", 2);
            sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
            sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
            return sph.ExecuteReader();
        }


        internal static IDataReader getRoleIDByUser(string userName)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "ct_Tuan_SelectOne", 1);
            sph.DefineSqlParameter("@ID_Tuan", SqlDbType.NVarChar, 100, ParameterDirection.Input, userName);
            return sph.ExecuteReader();
        }

        //tra ve id cua user
        public static IDataReader getUserIDByUser(string userName)
        {
            return SqlHelper.ExecuteReader(
            GetReadConnectionString(),
            CommandType.Text,
            "SELECT UserId FROM root_Users WHERE LoginName=N'" + userName + "'",
            null);
        }

        //tra ve id cua user
        public static IDataReader getUserRoleIDByUserID(Guid _userID)
        {
            return SqlHelper.ExecuteReader(
            GetReadConnectionString(),
            CommandType.Text,
            "SELECT ID FROM root_UserRoles WHERE UserID='" + _userID + "'",
            null);
        }

        internal static bool CheckRoleAuthenticatedByUserID(Guid _UserID, Guid _Role)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "root_UserRoles_CheckRoleAuthenticatedByUserID", 2);
            sph.DefineSqlParameter("@UserID", SqlDbType.UniqueIdentifier, ParameterDirection.Input, _UserID);
            sph.DefineSqlParameter("@RoleID", SqlDbType.UniqueIdentifier, ParameterDirection.Input, _Role);

            int rowsAffected = Convert.ToInt32(sph.ExecuteScalar());
            return (rowsAffected > 0);
        }

        internal static bool DeleteAllRoleOfUser(Guid userID)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "root_UserRoles_DeleteAllRoleOfUser", 1);
            sph.DefineSqlParameter("@UserID", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userID);
            int rowsAffected = Convert.ToInt32(sph.ExecuteScalar());
            return (rowsAffected > 0);
        }


        public static int GetCountByRoleID(Guid roleID)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "root_UserRoles_GetCountByRoleID", 1);
            sph.DefineSqlParameter("@RoleID", SqlDbType.UniqueIdentifier, ParameterDirection.Input, roleID);
            return Convert.ToInt32(sph.ExecuteScalar());
        }

        internal static IDataReader GetPageByRoleID(Guid roleID, int pageNumber, int pageSize, out int totalPages)
        {
            totalPages = 1;
            int totalRows = GetCountByRoleID(roleID);

            if (pageSize > 0)
                totalPages = totalRows / pageSize;

            if (totalRows <= pageSize)
            {
                totalPages = 1;
            }
            else
            {
                int remainder;
                Math.DivRem(totalRows, pageSize, out remainder);
                if (remainder > 0)
                {
                    totalPages += 1;
                }
            }
            SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "root_UserRoles_SelectPageByRoleID", 3);
            sph.DefineSqlParameter("@RoleID", SqlDbType.UniqueIdentifier, ParameterDirection.Input, roleID);
            sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
            sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
            return sph.ExecuteReader();
        }

        #region add by tsandtm 
        internal static IDataReader GetAllByUserID(Guid UserID)
        {
            SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "root_UserRoles_GetAllByUserID", 1);
            sph.DefineSqlParameter("@UserID", SqlDbType.UniqueIdentifier, ParameterDirection.Input, UserID);
            return sph.ExecuteReader();
        }
        #endregion
    }
}

